package io.renren.controller;

import com.google.gson.Gson;
import io.renren.entity.Reporttitle;
import io.renren.entity.WeixinUser;
import io.renren.service.ReporttitleService;
import io.renren.utils.Config;
import io.renren.utils.ConfigDb;
import io.renren.utils.R;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.*;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Created by zk on 2017/10/25.
 */

@Controller
@RequestMapping("crosstable")
public class CrossTableController {
	@Autowired
	private ReporttitleService reporttitleService;

	@RequestMapping("reportlist")
	@ResponseBody
	public R reporttitlelist() {
		List<Reporttitle> list = reporttitleService.queryList(null);
		return R.ok().put("reportlist", list);
	}

	@RequestMapping("getcrosstablelist")
	@ResponseBody
	public R crosstablelist(int type, String sdate, String edate) throws SQLException {
		System.out.println("-------  start 测试调用存储过程：有返回值且返回值为列表的");
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		String json = "";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(ConfigDb.getUrl(), ConfigDb.getUser(), ConfigDb.getPassword());

			stmt = conn.prepareCall("{call pro_reportmain1(?,?,?)}");

			stmt.setInt(1, type);
			stmt.setString(2, sdate);
			stmt.setString(3, edate);

			rs = stmt.executeQuery();

			// 判断是否有第二个结果集
			List<String> list = new ArrayList<>();
			if (stmt.getMoreResults()) {// 这个判断会自动指向下一个结果集
				rs1 = stmt.getResultSet();
				// 获取列名及类型
				int colunmCount1 = rs1.getMetaData().getColumnCount();
				String[] colNameArr1 = new String[colunmCount1];
				String[] colTypeArr1 = new String[colunmCount1];
				String str = new String();
				for (int i = 0; i < colunmCount1; i++) {
					colNameArr1[i] = rs1.getMetaData().getColumnName(i + 1);

					if (i < colunmCount1 - 1) {
						str += colNameArr1[i] + ",";
					} else {
						str += colNameArr1[i];
					}
				}
				System.out.print(str);
				list.add(str);

				System.out.println();
				while (rs1.next()) {

					StringBuffer sb = new StringBuffer();
					for (int i = 0; i < colunmCount1; i++) {
						if (i < colunmCount1 - 1) {
							sb.append(rs1.getString(i + 1) + " , ");
						} else {
							sb.append(rs1.getString(i + 1));
						}
					}
					list.add(sb.toString());
					System.out.println(sb);
				}

			}
			Gson gson = new Gson();
			json = gson.toJson(list);
			System.out.println(json);
			System.out.println("------- Test Proc Out is ResultSet end. ");

		} catch (Exception e) {
			e.printStackTrace(System.out);
		} finally {
			if (null != rs1) {
				rs1.close();
			}
			if (null != rs) {
				rs.close();
			}
			if (null != stmt) {
				stmt.close();
			}
			if (null != conn) {
				conn.close();
			}
		}
		return R.ok().put("crosstablelist", json);
	}

	// 导出数据到excel
	@SuppressWarnings("deprecation")
	@RequestMapping("/export")
	public String exportExcel(HttpServletResponse response, HttpServletRequest request) throws Exception {
		int type=Integer.valueOf(request.getParameter("type")).intValue();
		String headtitle=request.getParameter("title");
		String sdate=request.getParameter("sdate");
		String edate=request.getParameter("edate");
	// 如果是从数据库里面取数据 userList
	//	 List<WeixinUser> userList = weixinUserService.queryList(map);// 用户集合
             List<String> userList  =this.getList(type,sdate,edate);
            
             System.out.println("表结构："+userList);
		// 设置表头：对Excel每列取名(必须根据你取的数据编写)
		/*
		 * List<String>slist=new ArrayList<String>(); slist.add("组别"); for
		 * (WeixinUser string : userList) { slist.add(string.getNickname()); }
		 * slist.add("合计"); String[] tableHeader = slist.toArray(new
		 * String[slist.size()]);
		 * System.out.println(tableHeader+"++++++++++++");
		 */
		// String[] tableHeader = {"标签"};

		// 下面的都可以拷贝不用编写
             String[] title=null;
		// short cellNumber = (short) tableHeader.length;// 表的列数
		HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excel
		HSSFCell cell = null; // Excel的列
		HSSFRow row = null; // Excel的行
		HSSFCellStyle style = workbook.createCellStyle(); // 设置表头的类型
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFCellStyle style1 = workbook.createCellStyle(); // 设置数据类型
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont font = workbook.createFont(); // 设置字体
		HSSFSheet sheet = workbook.createSheet("统计表"); // 创建一个sheet
		HSSFHeader header = sheet.getHeader();// 设置sheet的头
		try {
			// **
			// 根据是否取出数据，设置header信息

			// *
			if (userList.size() < 1) {
				header.setCenter("查无资料");  
			} else {
				System.out.println("导出表格的尺寸=" + userList);
				header.setCenter("公司表");
				row = sheet.createRow(0);
				row.setHeight((short) 700);
				// sheet.addMergedRegion(new Region(1, (short)1, 1,
				// (short)(cellNumber-1)));
				int lenth = userList.size();

				cell = row.createCell(0);// 创建第0行第k列
				cell.setCellValue(headtitle);// 设置第0行第k列的值
				sheet.setColumnWidth(0, 5000);// 设置列的宽度
				font.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.
				font.setFontHeight((short) 500); // 设置单元字体高度
				style1.setFont(font);// 设置字体风格
				cell.setCellStyle(style1);
				style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 居中
				
				// 给excel填充数据这里需要编写
				// 设置合并的标题头(注意：横向合并的时候，标题头单元格必须长度和内容单元格一致否则合并时会把其他标题头单元格内容挤掉)
			//	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (int)title.length));// 横向：合并第一行的第2列到第4列
			//	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));// 横向：合并第一行的第2列到第4列
				//sheet.addMergedRegion(new CellRangeAddress(1, 1, lenth + 2, lenth + 3));// 横向：合并第一行的第2列到第4列
				WeixinUser user = null;
				// sheet.addMergedRegion(new Region(1, (short)1, 1,
				// (short)(2)));
				
				for (int i=0;i<userList.size();i++ ) {
					 title=userList.get(i).split(",");
					//标题
					if(i==0){
//						sheet.autoSizeColumn(i);//自动设宽 
	  					// user = (WeixinUser) userList.get(i);// 获取company对象
	  					row = sheet.createRow((short) (i + 2));// 创建第i+1行
	  					row.setHeight((short) 400);// 设置行高
	  					for (int j = 0; j < title.length; j++) {
	  					
	  	  						cell = row.createCell(j);// 创建第i+1行第0列
	  	  						cell.setCellValue(title[j]);// 设置第i+1行第0列的值
	  	  						cell.setCellStyle(style);// 设置风格
	  	  				
	  						
						}
					}else{
						String[] strs=userList.get(i).split(",");
						row = sheet.createRow((short) (i +2));// 创建第i+1行
	  					row.setHeight((short) 400);// 设置行高
	  					for (int k = 0; k < strs.length; k++) {
	  					
	  	  						cell = row.createCell(k);// 创建第i+1行第0列
	  	  						cell.setCellValue(strs[k]);// 设置第i+1行第0列的值
	  	  						cell.setCellStyle(style);// 设置风格
	  	  				
	  						
						}
					}
					
				}
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, title.length));// 横向：合并第一行的第2列到第4列
			/*	for (int i = 1; i < lenth; i++) {
					
  					sheet.autoSizeColumn(i);//自动设宽 
  					// user = (WeixinUser) userList.get(i);// 获取company对象
  					row = sheet.createRow((short) (i + 1));// 创建第i+1行
  					row.setHeight((short) 400);// 设置行高
  					for (int j = 0; j < 10; j++) {
  					
  						if (j==j) {
  							
  	  						cell = row.createCell(j);// 创建第i+1行第0列
  	  						cell.setCellValue(3);// 设置第i+1行第0列的值
  	  						cell.setCellStyle(style);// 设置风格
  	  					}
  						
  							
  						
					}
  					
  				}*/	
				Format f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date today = new Date();
				
				row = sheet.createRow((short) (1));
				row.setHeight((short) 400);

				cell = row.createCell(0);// 创建第i+1行第0列
				cell.setCellValue(f.format(today)+"~"+f.format(today));// 设置第i+1行第0列的值
				cell.setCellStyle(style);// 设置风格
			
				//当前时间
				cell = row.createCell(title.length-1);// 创建第i+1行第0列
				cell.setCellValue(f.format(today));// 设置第i+1行第0列的值
				cell.setCellStyle(style);// 设置风格

				/*
				 * row = sheet.createRow((short) (lenth + 1));// 创建第i+1行
				 * row.setHeight((short) 400);// 设置行高 cell = row.createCell(2);
				 * cell.setCellValue(user.getSex()+1000);
				 * cell.setCellStyle(style);
				 */

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 下面的可以不用编写，直接拷贝

		OutputStream out = null;// 创建一个输出流对象
		try {

			out = response.getOutputStream();//
			response.setHeader("Content-disposition", "attachment; filename=" + "company.xls");// filename是下载的xls的名，建议最好用英文
			response.setContentType("application/msexcel;charset=UTF-8");// 设置类型
			response.setHeader("Pragma", "No-cache");// 设置头
			response.setHeader("Cache-Control", "no-cache");// 设置头
			response.setDateHeader("Expires", 0);// 设置日期头
			workbook.write(out);
			out.flush();
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (out != null) {
					out.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return null;

	}
	public  List<String> getList(int type, String sdate, String edate) throws SQLException{
		System.out.println("-------  start 测试调用存储过程：有返回值且返回值为列表的");
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		List<String>list=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(ConfigDb.getUrl(), ConfigDb.getUser(), ConfigDb.getPassword());

			stmt = conn.prepareCall("{call pro_reportmain1(?,?,?)}");

			stmt.setInt(1, type);
			stmt.setString(2, sdate);
			stmt.setString(3, edate);

			rs = stmt.executeQuery();

			// 判断是否有第二个结果集
			 list = new ArrayList<>();
			if (stmt.getMoreResults()) {// 这个判断会自动指向下一个结果集
				rs1 = stmt.getResultSet();
				// 获取列名及类型
				int colunmCount1 = rs1.getMetaData().getColumnCount();
				String[] colNameArr1 = new String[colunmCount1];
				String str = new String();
				for (int i = 0; i < colunmCount1; i++) {
					colNameArr1[i] = rs1.getMetaData().getColumnName(i + 1);

					if (i < colunmCount1 - 1) {
						str += colNameArr1[i] + ",";
					} else {
						str += colNameArr1[i];
					}
				}
				System.out.print(str);
				list.add(str);
				while (rs1.next()) {

					StringBuffer sb = new StringBuffer();
					for (int i = 0; i < colunmCount1; i++) {
						if (i < colunmCount1 - 1) {
							sb.append(rs1.getString(i + 1) + " , ");
						} else {
							sb.append(rs1.getString(i + 1));
						}
					}
					list.add(sb.toString());
					System.out.println(sb);
				}
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace(System.out);
		} finally {
			if (null != rs1) {
				rs1.close();
			}
			if (null != rs) {
				rs.close();
			}
			if (null != stmt) {
				stmt.close();
			}
			if (null != conn) {
				conn.close();
			}
		
		}
		return list;
	}
}
